﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.Data;

namespace Yubis.Data.SqlServer
{
    public class Setup : ISetup
    {

        public Server SqlServer { get; set; }

        public string DatabaseName { get; set; }

        public Setup(string sqlServerName, string name)
        {
            DatabaseName = Yubis.Data.Properties.Settings.Default.DatabasePrefix + name;

            SqlServer = new Server(sqlServerName);
            SqlServer.ConnectionContext.Connect();
        }

        public Setup(string sqlServerName, string name, string username, string password)
        {

            DatabaseName = Yubis.Data.Properties.Settings.Default.DatabasePrefix + name;

            ServerConnection connection = new ServerConnection();

            connection.Password = password;
            connection.Login = username;
            connection.ServerInstance = sqlServerName;

            SqlServer = new Server(connection);
            SqlServer.ConnectionContext.Connect();
        }


        public void Run()
        {
            createDatabase();
            
            saveConnectionString();

            createTables();
        }

        public void DeleteDatabase()
        {

            SqlServer.KillDatabase(DatabaseName);
        }


        private void createDatabase()
        {
            Database database = new Database();
            database.Parent = SqlServer;
            database.Name = DatabaseName;
            database.Create();
        }

        private void saveConnectionString()
        {
            SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder(SqlServer.ConnectionContext.ConnectionString);
            connectionString.InitialCatalog = DatabaseName;

            Yubis.Data.Properties.Settings.Default.ConnectionString = connectionString.ConnectionString;
            Yubis.Data.Properties.Settings.Default.Save();

        }

        private void createTables()
        {
            createTableFieldChoice();
            createTableFieldMultiLevel();

            createTableLanguageList();
            createTableLanguageAvailable();
            createTableLanguageTranslation();

            createTableDataList();

            createTableSecurity();


            
        }

        private void createTableFieldChoice()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = new Table(database, "Field_Choice");

            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            table.Columns.Add(new Column(table, "Name", DataType.NVarChar(64)));
            table.Indexes.Add(new Index(table, "PK_Field_Choice"));
            table.Indexes["PK_Field_Choice"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_Field_Choice"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Field_Choice"], "Id"));

            table.Create();
        }

        private void createTableFieldMultiLevel()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = new Table(database, "Field_MultiLevel");

            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            table.Columns.Add(new Column(table, "Name", DataType.NVarChar(64)));
            table.Indexes.Add(new Index(table, "PK_Field_MultiLevel"));
            table.Indexes["PK_Field_MultiLevel"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_Field_MultiLevel"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Field_MultiLevel"], "Id"));

            table.Create();
        }

        private void createTableLanguageList()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = new Table(database, "Language_List");

            table.Columns.Add(new Column(table, "LocaleId", DataType.SmallInt));
            table.Columns.Add(new Column(table, "Name", DataType.NVarChar(64)));
            table.Indexes.Add(new Index(table, "PK_Language_List"));
            table.Indexes["PK_Language_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_Language_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Language_List"], "LocaleId"));

            table.Create();
        }

        private void createTableLanguageAvailable()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = new Table(database, "Language_Available");

            table.Columns.Add(new Column(table, "LocaleId", DataType.SmallInt));
            table.Columns.Add(new Column(table, "IsDefault", DataType.Bit));
            table.Indexes.Add(new Index(table, "PK_Language_Available"));
            table.Indexes["PK_Language_Available"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_Language_Available"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Language_Available"], "LocaleId"));

            table.Create();
        }

        private void createTableLanguageTranslation()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = new Table(database, "Language_Translation");

            table.Columns.Add(new Column(table, "ResourceId", DataType.UniqueIdentifier));
            table.Columns.Add(new Column(table, "LocaleId", DataType.SmallInt));
            table.Columns.Add(new Column(table, "Name", DataType.NVarChar(64)));
            table.Indexes.Add(new Index(table, "PK_Language_Translation"));
            table.Indexes["PK_Language_Translation"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_Language_Translation"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Language_Translation"], "ResourceId"));
            table.Indexes["PK_Language_Translation"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Language_Translation"], "LocaleId"));

            table.Create();
        }

        private void createTableDataList()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = new Table(database, "Data_List");

            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            table.Columns.Add(new Column(table, "Name", DataType.NVarChar(64)));
            table.Columns.Add(new Column(table, "SchemaInfo", DataType.Xml(string.Empty)));
            table.Columns.Add(new Column(table, "SecurityInfo", DataType.Xml(string.Empty)));
            table.Indexes.Add(new Index(table, "PK_Data_List"));
            table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));

            table.Create();
        }


        private void createTableSecurity()
        {
            Database database = SqlServer.Databases[DatabaseName];

            Table table = null;
                
            table = new Table(database, "Security_User");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();

            table = new Table(database, "Security_Role");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();

            table = new Table(database, "Security_UserRole");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();

            table = new Table(database, "Security_Data");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();


            table = new Table(database, "Security_DataField");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();


            table = new Table(database, "Security_DataRow");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();

            table = new Table(database, "Security_DataState");
            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            //table.Indexes.Add(new Index(table, "PK_Data_List"));
            //table.Indexes["PK_Data_List"].IndexKeyType = IndexKeyType.DriPrimaryKey;
            //table.Indexes["PK_Data_List"].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_Data_List"], "Id"));
            table.Create();


        }

    }
}
